package paq_sentencias;

import com.sun.rowset.CachedRowSetImpl;
import javax.sql.DataSource;


import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.ejb.Stateless;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;


public class Ejecutarsql {

    Statement st2 = null;
    Statement st = null;
    Connection con = null;
    ResultSet result1 = null;
    ResultSet result2 = null;
    ResultSet result3 = null;
    ResultSet rs = null;
    List l1 = null;
    public String men ;
    String men2 = "";

    public String getMen() {
        return men;
    }

    public void setMen(String men) {
        this.men = men;
    }

    public void ejecutar1(String sql) {
        try {
            InitialContext ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup("pool_proyecto");
            con = ds.getConnection();
            st = con.createStatement();
            // Statement st = con.createStatement();
            st.executeUpdate(sql);

            st.close();
            con.close();
            rs.close();

        } catch (SQLException ex) {
            Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
            men = "Transaccion no exitosa";

        } catch (NamingException ex) {
            Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
                con.close();
                rs.close();
                System.out.println("Conexion Cerrada con Exito...");
            } catch (SQLException ex) {
                Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }

    public void ejecutar2(String sql) {
        try {
            InitialContext ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup("pool_proyecto");
            con = ds.getConnection();
            st = con.createStatement();
            st.executeUpdate(sql);
            st.close();
            con.close();
            setMen("Exitoso");
              System.out.println("Exitoso...");
        } 
        
        catch (SQLException ex) {
            Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
            setMen(ex.getMessage());
            System.out.println("no Exitoso...");
           
        } catch (NamingException ex) {
            Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
           // setMen("Transaccion no exitosa");
        } finally {
            try {
                st.close();
                con.close();
//                setMen("Exitoso");
                
                
                System.out.println("Conexion Cerrada con Exito...");
            } catch (SQLException ex) {
                Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
                
            }
        }

    }
   

//    public List listar(String sql) {
//
//        try {
//            InitialContext ic = new InitialContext();
//            DataSource ds = (DataSource) ic.lookup("pool_tesis");
//            con = ds.getConnection();
//            st2 = con.createStatement();
//            System.out.println("Se ha realizado con exito la conexión a MySQL");
//            rs = st2.executeQuery(sql);
//            l1 = (List) rs;
//
//        } catch (SQLException ex) {
//            Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
//        } catch (NamingException ex) {
//            Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
//        } finally {
//            try {
//                con.close();
//                //    st2.close();
//                System.out.println("Conexion Cerrada con Exito...");
//            } catch (SQLException ex) {
//                Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
//            }
//        }
//
//        return l1;
//    }
    public CachedRowSetImpl rellena2(String query) throws Exception {
        InitialContext ic = new InitialContext();
        DataSource ds = (DataSource) ic.lookup("pool_proyecto");
        CachedRowSetImpl crs = null;
        Connection conBBDD = null;
        try {
            conBBDD = ds.getConnection();
            crs = new CachedRowSetImpl();
            crs.setCommand(query);
            crs.execute(conBBDD);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conBBDD != null) {
                try {
                    conBBDD.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return crs;
    }

    
}
